--
-- @Author:      name
-- @DateTime:    2018-03-30 23:05:48
-- @Description: 数据库管理

local skynet = require "skynet"
local log = require "Log"
local redis = require "redis"
local dbname = skynet.getenv("mysql_db")

local config_table = {}
local user_table = {}
local common_table = {}

local schema = {}
local CMD = {}


local TbFactory = require "entitybase.EntityFactory"

local Command = class("Command")


---------------------------------------------------------
-- Private
---------------------------------------------------------
function Command:ctor(message)
	self.msg = message
	self.tables = {}
	self:register()
end

function Command:register()
	self.msg:registerSelf('start',handler(self,self.start))
	self.msg:registerSelf('stop',handler(self,self.stop))
	self.msg:registerSelf('get',handler(self,self.get))
	self.msg:registerSelf('add',handler(self,self.add))
	self.msg:registerSelf('delete',handler(self,self.delete))
	self.msg:registerSelf('update',handler(self,self.update))	
end

--获取表的主键
function Command:getPrimaryKey(tbname)
	local sql = "select k.column_name " ..
		"from information_schema.table_constraints t " ..
		"join information_schema.key_column_usage k " ..
		"using (constraint_name,table_schema,table_name) " ..
		"where t.constraint_type = 'PRIMARY KEY' " ..
		"and t.table_schema= '".. dbname .. "'" ..
		"and t.table_name = '" .. tbname .. "'"	
	local t = skynet.call(self.mysql, "lua", "execute",sql)
	
	if not next(t) then 		
		log.debug(tbname.."表没有主键______")
		return nil
	end
	return t[1]["column_name"]
end

--取表的字段
function Command:getFields(tbname)
	local sql = string.format("select column_name from information_schema.columns where table_schema = '%s' and table_name = '%s'", dbname, tbname)
	local rs = skynet.call(self.mysql, "lua", "execute", sql)
	local fields = {}
	for _, row in pairs(rs) do
		table.insert(fields, row["column_name"])
	end
	return fields
end

--取字段类型
function Command:getFieldType(tbname, field)
	local sql = string.format("select data_type from information_schema.columns where table_schema='%s' and table_name='%s' and column_name='%s'",
			dbname, tbname, field)
	local rs = skynet.call(self.mysql, "lua", "execute", sql)
	return rs[1]["data_type"]
end

--取数据库表与字段入到schema中
function Command:load_schema_to_redis()
	-- local sql = "select table_name from information_schema.tables where table_schema='" .. dbname .. "'"
	-- local rs = skynet.call(self.mysql, "lua", "execute", sql)
	-- print("_______load_schema_to_redis___",rs)
	-- for _, row in pairs(rs) do
	for tbname,v in pairs(self.tables) do 
		-- local tbname =row.table_name
		schema[tbname] = {}
		schema[tbname]["fields"] = {}
		schema[tbname]["pk"] = self:getPrimaryKey(tbname)

		local fields = self:getFields(tbname)
		for _, field in pairs(fields) do
			local field_type = self:getFieldType(tbname, field)
			if field_type == "char"
			  or field_type == "varchar"
			  or field_type == "tinytext"
			  or field_type == "text"
			  or field_type == "mediumtext"
			  or field_type == "longtext" then
				schema[tbname]["fields"][field] = "string"
			else
				schema[tbname]["fields"][field] = "number"
			end
		end
	end

end

--转换
function Command:converRecord(tbname, record)
	for k, v in pairs(record) do		
		if schema[tbname] and schema[tbname]["fields"][k] == "number" then
			record[k] = tonumber(v)
		end
	end

	return record
end

--表中的几个字段做为键在redis中显示
function Command:makeRedisKey(row, key)
	local rediskey = ""
	local fields = string.split(key, ",")
	for i, field in pairs(fields) do
		if i == 1 then
			rediskey = row[field]
		else
			rediskey = rediskey .. ":" .. row[field]
		end
	end
	return rediskey
end

function Command:loadData(config, uid)
	local tbname = config.name
	--local pk = schema[tbname]["pk"]
	local pk = config.pk
	local offset = 0
	local sql
	local data = {}
	local key = config.key
	if not uid then
		if not config.columns then
			sql = string.format("select * from %s order by %s asc limit %d, 1000", tbname, pk, offset)
		else
			sql = string.format("select %s from %s order by %s asc limit %d, 1000", config.columns, tbname, pk, offset)
		end
	else
		if not config.columns then
			sql = string.format("select * from %s where "..key.." = %d order by %s asc limit %d, 1000", tbname, uid, pk, offset)
		else
			sql = string.format("select %s from %s where "..key.." = %d order by %s asc limit %d, 1000", config.columns, tbname, uid, pk, offset)
		end
	end
	-- if uid ==10499 then 
	-- 	log.debug("____________________________",sql)
	-- end
	local rs = skynet.call(self.mysql, "lua", "execute", sql)
	-- print(rs)
	if #rs <= 0 then 
		return data
	end
	for _, row in pairs(rs) do
		local rediskey = self:makeRedisKey(row, config.key)
		self:do_redis({ "hmset", tbname .. ":" .. rediskey, row, true }, uid)
		-- 建立索引
		if config.indexkey then
			local indexkey = self:makeRedisKey(row, config.indexkey)
			self:do_redis({ "zadd", tbname .. ":index:" .. indexkey, 0, rediskey }, uid) 
		end

		table.insert(data, row)			
	end
	return data
end

--加载主键最大值到redis
function Command:load_maxkey_impl(tbname)
	local pk = schema[tbname]["pk"]
	local sql = string.format("select max(%s) as maxkey from %s", pk, tbname)
	local result = skynet.call(self.mysql, "lua", "execute", sql)
	if #result > 0 and not table.empty(result[1]) then
		self:do_redis({ "set", tbname .. ":" .. pk, result[1]["maxkey"] })
	end
end

function Command:load_maxkey()
	for k, v in pairs(user_table) do
		self:load_maxkey_impl(k)
	end

	for k, v in pairs(common_table) do
		self:load_maxkey_impl(k)
	end

end

--redis结果转为table
function Command:redisToTable(t, fields)
	if not t then 
		return 
	end
	-- assert(type(t) == "table", "_____error________redisToTable t is not table")
	local data = {}
	if not fields then
		for i=1, #t, 2 do
			data[t[i]] = t[i+1]
		end
	else
		for i=1, #t do
			data[fields[i]] = t[i]
		end
	end
	return data
end

function Command:do_redis(args, uid)
    local cmd = args[1]
    args[1] = uid
    if not self.redis then 
    	--不使用redis
    	return 
    end
    return skynet.call(self.redis, "lua", cmd, table.unpack(args))
end
----------------------------------------------------------------------
--CMD
----------------------------------------------------------------------
function Command:start(tables,noUseRedis)
	log.debug("__databases_Command:start____")

	--mysql操作服务
	local mysqldata = {host = skynet.getenv("mysql_host"),
				  port = tonumber(skynet.getenv("mysql_port")),				  
				  user = skynet.getenv("mysql_user"),
				  password = skynet.getenv("mysql_pwd"),
				  database = skynet.getenv("mysql_db"),
				  serviceName = "mysql_service",
				 }
	self.mysql = skynet.newservice("mysql_service")
	skynet.call(self.mysql, "lua", "start", mysqldata)

	if not noUseRedis then 
		--redis操作服务
		self.redis = skynet.uniqueservice("redis_service")
		skynet.call(self.redis, "lua", "start")
	end
	--异步mysql操作
	self.dbsync = skynet.uniqueservice("dbsync_service")
	skynet.call(self.dbsync, "lua", "start")
	self.factory = TbFactory.new(self)
	for _, v in pairs(tables) do
		self.tables[v.name] = v	
	end
	self:load_schema_to_redis()
	--self:load_data_to_redis()
	for name,tb in pairs(self.tables) do 
		--表对象
		local entity = self.factory:get(name)
		entity:init(tb.pk,tb.key,tb.indexkey)	
	end
end

function Command:stop()
end

-- 从redis获取user类型表单行数据，如果不存在，则从mysql加载
-- fields为空，获取整行
function Command:get(tbname, uid, fields)
	local result
	if uid then 
		if fields then
			result = self:do_redis({ "hmget", tbname .. ":" .. uid, table.unpack(fields) }, uid)
			result = self:redisToTable(result, fields)
		else			
			result = self:do_redis({ "hgetall", tbname .. ":" .. uid }, uid)
			result = self:redisToTable(result)
		end
	end
	-- redis没有数据返回，则从mysql加载
	if table.empty(result) then
		local config = self.tables[tbname]
		local data = self:loadData(config, uid)
		local t = data
		--assert(#t <= 1)
		if #data == 1 then
			t = data[1]
		else
			return data
		end		
		if fields and not next(t) then
			result = {}
			for k, v in pairs(fields) do
				result[v] = t[v]
			end
		else
			result = t
		end
	end
	result = self:converRecord(tbname, result)

	return result
end

----------------------------------------------------------------------
--数据库操作
----------------------------------------------------------------------

-- redis中增加一行记录，并同步到mysql
function Command:add(obj, row, nosync)
	local tbname = obj.tbname
	local pk = obj.pk
	local key = obj.key
	local indexkey = obj.indexkey
	local rediskey = self:makeRedisKey(row, key)
	local uid = row[key]
	--添加时不用增加到Redis里
	-- self:do_redis({ "hmset", tbname .. ":" .. rediskey, row }, uid)
	-- if indexkey and indexkey ~= "" then
	-- 	local linkey = self:makeRedisKey(row,indexkey)
	-- 	self:do_redis({ "zadd", tbname..":index:"..linkey, 0, rediskey }, uid)
	-- end
	local columns
	local values
	local val
	for k, v in pairs(row) do			
		if type(v) == 'boolean' then 
			val = 0
			if v then
				val = 1
			end
		elseif type(v) == 'number' then 
			val = v				
		else
			val = "'" .. v .. "'"
		end

		if not columns then
			columns = k
		else
			columns = columns .. "," .. k
		end
		
		if not values then
			values = ""..val
		else
			values = values .. "," .. val
		end
	end
	local sql = "insert into " .. tbname .. "(" .. columns .. ") values(" .. values .. ")"		
	log.debug(sql)
	if not nosync then		
		skynet.call(self.dbsync, "lua", "sync", sql)
	else
		skynet.call(self.mysql, "lua", "execute", sql)
	end

	return true
end

-- redis中删除一行记录，并异步到mysql
function Command:delete(obj, row, nosync)

	local tbname = obj.tbname
	local pk = obj.pk
	local key = obj.key
	local indexkey = obj.indexkey
	local rediskey = self:makeRedisKey(row, key)
	--local pk = schema[tbname]["pk"]	
	local uid = row[key]
	self:do_redis({ "del", tbname .. ":" .. rediskey }, uid)
	if indexkey and indexkey ~= "" then
		local linkey = self:makeRedisKey(row,indexkey)
		self:do_redis({ "zrem", tbname .. ":index:" .. linkey }, uid)
	end
	local sql = "delete from " .. tbname.. " where " .. key .. "=" .. "'" .. row[key] .. "'"
	if not nosync then		
		skynet.call(self.dbsync, "lua", "sync", sql)
	else
		skynet.call(self.mysql, "lua", "execute", sql)
	end
	return true

end

-- redis中更新一行记录，并异步到mysql
function Command:update(obj, row, nosync)
	local tbname = obj.tbname
	local pk = obj.pk
	local key = obj.key
	local indexkey = obj.indexkey
	local rediskey = self:makeRedisKey(row, key)
	local uid = row[key]
	self:do_redis({ "hmset", tbname .. ":" .. rediskey, row }, uid)
	if row.diamond then 
		log.debug("____数据库______Command______update___"..uid.." "..row.diamond)
	end
	local setvalues = ""
	for k, v in pairs(row) do
		setvalues = setvalues .. k .. "='" .. v .. "',"
	end
	setvalues = setvalues:trim(",")

	local sql = "update " .. tbname .. " set " .. setvalues .. " where " .. key .. "='" .. row[key] .. "'"
	if not nosync then		
		skynet.call(self.dbsync, "lua", "sync", sql)
	else
		skynet.call(self.mysql, "lua", "execute", sql)
	end

	return true
end

-- redis中删除一行记录，并异步到mysql
function Command:unload(obj, row, nosync)

	local tbname = obj.tbname
	local pk = obj.pk
	local key = obj.key
	local indexkey = obj.indexkey
	local rediskey = self:makeRedisKey(row, key)
	local uid = row[key]
	self:do_redis({ "del", tbname .. ":" .. rediskey }, uid)
	-- print("____del__",tbname,rediskey,uid)
	if indexkey and indexkey ~= "" then
		local linkey = self:makeRedisKey(row,indexkey)
		self:do_redis({ "zrem", tbname .. ":index:" .. linkey }, uid)
	end

	return true

end





return Command